﻿using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TR.Domain.Entity.BsItemType;
using TR.Domain.Entity;
using TR.Domain.Repository.sqlDto;
using TR.Domain.Repository.BsItemRepository;
using Microsoft.EntityFrameworkCore;
using static System.Net.Mime.MediaTypeNames;

namespace TR.EFCore.Repository.BsItemRepository
{
    public class BsitemRepostiory:IItemIRepository
    {
        private readonly MyContext myContext;
        public BsitemRepostiory(MyContext myContext)
        {
            this.myContext = myContext;
        }
        /// <summary>
        ///查询
        /// </summary>
        /// <returns></returns>
        public Task<List<bs_Item>> ItemSelete()
        {
            return myContext.bs_Item.ToListAsync();
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public async Task<FenYe<wh_orderbuy_d>> wh_Orderbuy_d_FenYe(int pageSize, int currentPage, string str)
        {
            using (SqlConnection con = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@pageSize", pageSize);
                dynamicParameters.Add("@keyName", "id");
                dynamicParameters.Add("@tableName", "JHXQ");
                dynamicParameters.Add("@currentPage", currentPage);
                dynamicParameters.Add("@where", str);
                dynamicParameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
                string sql = "exec [dbo].[procFenYe_Where] @pageSize, @keyName, @tableName, @currentPage, @where, @rows out";
                FenYe<wh_orderbuy_d> fenYe = new FenYe<wh_orderbuy_d>();
                fenYe.List = await con.QueryAsync<wh_orderbuy_d>(sql, dynamicParameters);
                fenYe.Rows = dynamicParameters.Get<int>("rows");
                return fenYe;
            }
        }
        /// <summary>
        /// Dapper多表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public async Task<FenYe<bs_Item>> bs_Item_FenYe(int pageSize, int currentPage, string str)
        {
            using (SqlConnection con = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@pageSize", pageSize);
                dynamicParameters.Add("@keyName", "id");
                dynamicParameters.Add("@tableName", "bs_Item");
                dynamicParameters.Add("@currentPage", currentPage);
                dynamicParameters.Add("@where", str + " AND is_del <> 1"); // 添加条件，is_del 不等于 1
                dynamicParameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
                string sql = "exec [dbo].[procFenYe_Where] @pageSize, @keyName, @tableName, @currentPage, @where, @rows out";
                FenYe<bs_Item> fenYe = new FenYe<bs_Item>();
                fenYe.List = await con.QueryAsync<bs_Item>(sql, dynamicParameters);
                fenYe.Rows = dynamicParameters.Get<int>("rows");
                return fenYe;
            }
        }
        /// <summary>
        /// 新增查询
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="currentPage"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        public async Task<FenYe<bs_Item>> bs_Item_xz_FenYe(int pageSize, int currentPage, string str)
        {
            using (SqlConnection con = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@pageSize", pageSize);
                dynamicParameters.Add("@keyName", "id");
                dynamicParameters.Add("@tableName", "bs_Item");
                dynamicParameters.Add("@currentPage", currentPage);
                dynamicParameters.Add("@where", str);
                dynamicParameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
                string sql = "exec [dbo].[procFenYe_Where] @pageSize, @keyName, @tableName, @currentPage, @where, @rows out";
                FenYe<bs_Item> fenYe = new FenYe<bs_Item>();
                fenYe.List = await con.QueryAsync<bs_Item>(sql, dynamicParameters);
                fenYe.Rows = dynamicParameters.Get<int>("rows");
                return fenYe;
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public async Task<bool> ItemAdd(bs_Item Item)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"Insert into [dbo].[bs_Item](item_type_code,item_code,item_name,item_type,sort_code,inbill_state,model_code,outbill_price,inbill_price,item_up,item_down,below_down,above_up,unit,is_del)" +
                $"Values('{Item.item_type_code}','{Item.item_code}','{Item.item_name}','{Item.item_type}','{Item.sort_code}',{Item.inbill_state},'{Item.model_code}','{Item.outbill_price}','{Item.inbill_price}',{Item.item_up},{Item.item_down},'{Item.below_down}','{Item.above_up}','{Item.unit}',0)";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="Item"></param>
        /// <returns></returns>
        public async Task<bool> ItemUDelect(string item_code)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"delete from bs_Item where [bs_Item] = '{item_code}'";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> ItemDelect(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"UPDATE bs_Item Set [is_del] = 1 where [id] = '{id}' and is_del=0";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        /// <summary>
        /// 查询id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bs_Item> ItemSelectid(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_Item] where [id] = @id";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_Item>(sql, new { id = id });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
        public async Task<bs_Item> ItemSelectCode(string item_code)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_Item] where [item_code] = @item_code";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_Item>(sql, new { item_code = item_code });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
        public async Task<bool> ItemUpdate(UpdateItemDto uid)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"Update [dbo].[bs_Item] Set [item_type_code] ='{uid.item_type_code}' ,[item_code]='{uid.item_code}',[item_name]='{uid.item_name}',[item_type]='{uid.item_type}',[sort_code]='{uid.sort_code}',[inbill_state]='{uid.inbill_state}',[model_code]='{uid.model_code}',[outbill_price]='{uid.outbill_price}', [inbill_price]='{uid.inbill_price}',[item_up]='{uid.item_up}',[item_down]='{uid.item_down}',[below_down]='{uid.below_down}',[above_up]='{uid.above_up}',[unit]='{uid.unit}',[is_del]='{uid.is_del}' where [id] ='{uid.id}' ";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        //public async Task<IEnumerable<Image>> GetAllByParentIdAsync(int parentId)
        //{
            //return (IEnumerable<Image>)await myContext.itemImages.Where(i => i.ParentId == parentId).ToListAsync();
        //}

   /*     public async Task AddAsync(ItemImage image)
        {
            await myContext.itemImages.AddAsync(image);
        }

        public async Task SaveChangesAsync()
        {
            await myContext.SaveChangesAsync();
        }*/
    }
}
